﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Film.Common;

namespace Film.Repository
{
    public class tblMovieRepository : Repository<tblMovie>
    {

        public tblMovie GetByMovieID(int movieID)
        {
            return dataContext.tblMovies.FirstOrDefault(x => x.MovieID == movieID);
        }

        public List<tblMovie> Filter(string keyWord, int start, int limit, out int count, bool? status = null)
        {
            List<tblMovie> listResultTemp = new List<tblMovie>();
            keyWord = UtilString.XacDinhNguyenAm(keyWord);
            if (status == null)
            {
                var result = (from ds in dataContext.tblMovies
                              where (keyWord == "" ||
                              (keyWord != "" && (System.Data.Linq.SqlClient.SqlMethods.Like(ds.MovieName, keyWord + "%"))))
                              select ds);

                count = result.Count();
                listResultTemp= result.Skip(start).Take(limit).ToList();
            }
            else
            {
                var result = (from ds in dataContext.tblMovies
                              where (keyWord == "" ||
                              (keyWord != "" && (System.Data.Linq.SqlClient.SqlMethods.Like(ds.MovieName, keyWord + "%"))))
                              && (ds.Status == status.Value)
                              select ds);

                count = result.Count();
                listResultTemp= result.Skip(start).Take(limit).ToList();
            }

            listResultTemp.ForEach(x=>x.CountryName = x.tblCountry!=null ? x.tblCountry.CountryName : "");
            return listResultTemp;

        }

        public List<tblMovieTemp> FilterList(int start, int limit, out int count, bool? status = null)
        {
            //keyWord = UtilString.XacDinhNguyenAm(keyWord);
            List<tblMovieTemp> listResultTemp = new List<tblMovieTemp>();

            if (status == null)
            {
                var result = (from ds in dataContext.tblMovies
                              select new tblMovieTemp
                              {
                                  MovieID = ds.MovieID,
                                  MovieName = ds.MovieName,
                                  Year = ds.Year,
                                  ToTalRatingSum = (ds.TotalRating == 0 ? 0 : (int)Math.Ceiling((double)ds.SumRating / ds.TotalRating)),
                                  ImageUrl = ds.ImageUrl,
                                  Director = ds.Director,
                                  Genre = ds.Genre,
                                  Actor = ds.Actor.Length>40? ds.Actor.Substring(0,40) : ds.Actor
                              });

                count = result.Count();
                listResultTemp = result.Skip(start).Take(limit).ToList();
            }
            else
            {
                var result = (from ds in dataContext.tblMovies
                              where (ds.Status == status.Value)
                              select new tblMovieTemp
                              {
                                  MovieID = ds.MovieID,
                                  MovieName = ds.MovieName,
                                  Year = ds.Year,
                                  ToTalRatingSum = (ds.TotalRating == 0 ? 0 : (int)Math.Ceiling((double)ds.SumRating / ds.TotalRating)),
                                  ImageUrl = ds.ImageUrl,
                                  Director = ds.Director,
                                  Genre = ds.Genre,
                                  Actor = ds.Actor.Length > 40 ? ds.Actor.Substring(0, 40) : ds.Actor
                              });

                count = result.Count();
                listResultTemp = result.Skip(start).Take(limit).ToList();

            }
             listResultTemp.ForEach(x=>x.CountryName = x.tblCountry!=null ? x.tblCountry.CountryName : "");
            return listResultTemp;
        }

        public List<tblMovieTemp> Filter(string movieName, string director, string year, string genre, int start, int limit, out int count, bool status = true)
        {
            List<tblMovieTemp> listResultTemp = new List<tblMovieTemp>();
            int iYear = 0;
            int.TryParse(year, out iYear);

            int iGenre = 0;
            int.TryParse(genre, out iGenre);

            movieName = UtilString.XacDinhNguyenAm(movieName);
            director = UtilString.XacDinhNguyenAm(director);

            var result = (from ds in dataContext.tblMovies
                          where (movieName == "" ||
                          (movieName != "" && (System.Data.Linq.SqlClient.SqlMethods.Like(ds.MovieName, "%" + movieName + "%"))))
                          && (director == "" ||
                          (director != "" && (System.Data.Linq.SqlClient.SqlMethods.Like(ds.Director, "%" + director + "%"))))
                          && (iYear == 0 || (iYear != 0 && ds.Year == iYear))
                         && (genre == "--Tất cả--" ||
                          (genre != "--Tất cả--" && (System.Data.Linq.SqlClient.SqlMethods.Like(ds.Genre, "%" + genre + "%"))))
                          && (ds.Status == status)
                          select new tblMovieTemp
                          {
                              MovieID = ds.MovieID,
                              MovieName = ds.MovieName,
                              Year = ds.Year,
                              ToTalRatingSum = (ds.TotalRating == 0 ? 0 : (int)Math.Ceiling((double)ds.SumRating / ds.TotalRating)),
                              ImageUrl = ds.ImageUrl,
                              Director = ds.Director,
                              Genre = ds.Genre,
                              Actor = ds.Actor.Length > 40 ? ds.Actor.Substring(0, 40) : ds.Actor
                          });

            count = result.Count();
            listResultTemp = result.Skip(start).Take(limit).ToList();

            listResultTemp.ForEach(x => x.CountryName = x.tblCountry != null ? x.tblCountry.CountryName : "");
            return listResultTemp;
        }

        public List<tblMovieTemp> GetAllOrderbyRating(int limit)
        {
            return (from ds in dataContext.tblMovies
                        where ds.Status
                        orderby ds.SumRating descending
                        select new tblMovieTemp
                          {
                              MovieID = ds.MovieID,
                              MovieName = ds.MovieName,
                              Year = ds.Year,
                              ToTalRatingSum = (ds.TotalRating == 0 ? 0 : (int)Math.Ceiling((double)ds.SumRating / ds.TotalRating)),
                              ImageUrl = ds.ImageUrl,
                              Director = ds.Director,
                              Genre = ds.Genre,
                              Actor = ds.Actor.Length > 40 ? ds.Actor.Substring(0, 40) : ds.Actor,
                              CountryName = ds.tblCountry!=null? ds.tblCountry.CountryName : ""
                          }).Take(limit).ToList();
        }

        #region A


        //Lấy tất cả Film và đánh giá của phim này

        //Từng độ tuổi thì tính trung bình vào đó

        /// <summary>
        /// Lấy nhưng phim đã đánh giá
        /// </summary>
        /// <returns></returns>
        public List<tblMovie> GetAllRecomment(int userID)
        {
            var result = (from ds in dataContext.tblMovies
                          join qd in dataContext.tblRatings
                          on ds.MovieID equals qd.MovieID
                          where qd.UserID == userID
                          select ds);

            return result.ToList();

        }

        public List<tblMovie> GetAllNotRecomment(int userID, int start, int limit, out int count)
        {
            List<tblMovie> listResultTemp = new List<tblMovie>();

            List<tblMovie> listRecomment = GetAllRecomment(userID);
            List<string> listIDMovie = listRecomment.ConvertAll<string>(delegate(tblMovie x)
            {
                return x.MovieID.ToString();
            });

            string strListID = ";" + string.Join(";", listIDMovie.ToArray()) + ";";
            var result = (from ds in dataContext.tblMovies
                          where System.Data.Linq.SqlClient.SqlMethods.Like(strListID, "%;" + ds.MovieID + ";%") == true && ds.Status
                          select ds);
            count = result.Count();
            listResultTemp= result.Skip(start).Take(limit).ToList();


            listResultTemp.ForEach(x => x.CountryName = x.tblCountry != null ? x.tblCountry.CountryName : "");
            return listResultTemp;
        }

        public List<tblMovieTemp> GetAllTempMovies()
        {
            List<tblMovieTemp> listResultTemp = new List<tblMovieTemp>();
            var result = (from ds in dataContext.tblMovies
                          where ( ds.Status)
                          select new tblMovieTemp
                          {
                              MovieID = ds.MovieID,
                              MovieName = ds.MovieName,
                              Year = ds.Year,
                              ToTalRatingSum = (ds.TotalRating == 0 ? 0 : (int)Math.Ceiling((double)ds.SumRating / ds.TotalRating)),
                              ImageUrl = ds.ImageUrl,
                              Director = ds.Director,
                              Genre = ds.Genre,
                              Actor = ds.Actor.Length > 40 ? ds.Actor.Substring(0, 40) : ds.Actor
                              
                          });

            listResultTemp = result.ToList();

            listResultTemp.ForEach(x => x.CountryName = x.tblCountry != null ? x.tblCountry.CountryName : "");
            return listResultTemp;
        }
        public List<tblMovieTemp> GetAllTempNotRecomment(int userID, int start, int limit, out int count)
        {
            List<tblMovieTemp> listResultTemp = new List<tblMovieTemp>();

            List<tblMovie> listRecomment = GetAllRecomment(userID);
            List<string> listIDMovie = listRecomment.ConvertAll<string>(delegate(tblMovie x)
            {
                return x.MovieID.ToString();
            });

            string strListID = ";" + string.Join(";", listIDMovie.ToArray()) + ";";
            //var result = (from ds in dataContext.tblMovies
            //              where System.Data.Linq.SqlClient.SqlMethods.Like(strListID, "%;" + ds.MovieID + ";%") == true && ds.Status
            //              select ds);
            //count = result.Count();
            //listResultTemp = result.Skip(start).Take(limit).ToList();

            var result = (from ds in dataContext.tblMovies
                          where (System.Data.Linq.SqlClient.SqlMethods.Like(strListID, "%;" + ds.MovieID + ";%") == false && ds.Status)
                          select new tblMovieTemp
                          {
                              MovieID = ds.MovieID,
                              MovieName = ds.MovieName,
                              Year = ds.Year,
                              ToTalRatingSum = (ds.TotalRating == 0 ? 0 : (int)Math.Ceiling((double)ds.SumRating / ds.TotalRating)),
                              ImageUrl = ds.ImageUrl,
                              Director = ds.Director,
                              Genre = ds.Genre,
                              Actor = ds.Actor.Length > 40 ? ds.Actor.Substring(0, 40) : ds.Actor
                          });

            count = result.Count();
            listResultTemp = result.Skip(start).Take(limit).ToList();

            listResultTemp.ForEach(x => x.CountryName = x.tblCountry != null ? x.tblCountry.CountryName : "");
            return listResultTemp;
        }

        #endregion A
    }
}
